package com.kgc.dao;

import com.kgc.entity.Building;
import com.kgc.entity.Danyuan;
import com.kgc.entity.Room;
import com.kgc.entity.UserRoom;
import com.kgc.utils.PageUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserRoomDao extends BaseDao {


    /**
     * 查询房屋使用者
     *
     * @param userId
     * @return roomName List
     */
    public List<String> selectRoomByUserId(Integer userId) {
        List<String> list = new ArrayList<>();
        try {
            String sql = "select CONCAT(d.name,r.name) from user_room ur inner join room r on ur.room_id = r.id inner join danyuan d on d.id = r.danyuan_id where  ur.user_id=? and  ur.status = 1";
            ResultSet rs = super.getExecuteQuery(sql, userId);
            while (rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    /**
     * 查询房屋使用者
     *
     * @param roomId
     * @return
     */
    public List<UserRoom> selectUserByRoom(PageUtils pageUtils, String roomId) {
        List<UserRoom> list = new ArrayList<>();
        try {
            String sql = "select ur.*,u.username from user_room ur inner join user u on ur.user_id = u.id  where ur.room_id=?  limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, roomId, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserRoom(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5), rs.getString(6)));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    /**
     * 查询房屋总数
     *
     * @param roomId
     * @return
     */
    public int totalCount(String roomId) {
        try {
            String sql = "select count(*) from user_room ur inner join user u on ur.user_id = u.id  where ur.room_id=?";
            ResultSet rs = super.getExecuteQuery(sql, roomId);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int stopRoom(String id, String room) {
        String str1 = room.substring(0, 4);
        String str2 = room.substring(4);
        String sql1 = "DELETE FROM user_room  WHERE user_id = ? and room_id = " +
                "(SELECT id FROM room   WHERE `name`= ? and danyuan_id = (select id from danyuan where `name` =?))";
        int i = super.getExecuteUpdate(sql1, id, str2, str1);
        if (i > 0) {
            String sql2 = "UPDATE room SET `status` = 0  WHERE `name`= ? and danyuan_id = (select id from danyuan where `name` = ? )";
            return super.getExecuteUpdate(sql2, str2, str1);
        }
        return 0;

    }

    public List<String> selectRoomByStatus() {
        List<String> list = new ArrayList<String>();
        String sql = "SELECT CONCAT(d.`name`,r.`name`) from room r LEFT JOIN danyuan d on r.danyuan_id = d.id  WHERE r.`status`=0";
        try {
            ResultSet rs = super.getExecuteQuery(sql);
            while (rs.next()) {
                list.add(rs.getString(1));
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }


    public List<Danyuan> selectDanyuanRoom() {
        List<Danyuan> list = new ArrayList<>();
        try {
            String sql = "select * from danyuan where id in ( select DISTINCT danyuan_id from room  where status = 0)";
            ResultSet rs = super.getExecuteQuery(sql);
            while (rs.next()) {
                list.add(new Danyuan(rs.getInt(1), rs.getString(2), rs.getInt(3)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;

    }

    public List<Room> selectRoomByDanYuan(int did) {
        List<Room> list = new ArrayList<>();
        try {
            String sql = "select * from room  where danyuan_id = ? and status = 0 ";
            ResultSet rs = super.getExecuteQuery(sql, did);
            while (rs.next()) {
                list.add(new Room(rs.getInt(1), rs.getString(2), rs.getString(3),
                        String.valueOf(rs.getInt(4)), rs.getInt(5)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;

    }

    public int updateRoomStatus(int id) {
        String sql = "UPDATE `room` SET `status` = 1 WHERE `id` = ?";
        return super.getExecuteUpdate(sql, id);
    }

    public int addUserRoom(UserRoom userRoom) {
        String sql = "INSERT INTO `user_room`(`id`, `user_id`, `room_id`, `inTime`, `outTime`, `status`) VALUES (null, ?, ?, NULL, NULL, 1);\n";
        return super.getExecuteUpdate(sql, userRoom.getUserId(), userRoom.getRoomId());
    }
}
